-- 创建存储过程
CREATE OR REPLACE PROCEDURE p_calculate_holding_days(IN p_acctid int)
--RETURNS SETOF temp_holding_days
LANGUAGE plpgsql AS $$
DECLARE
    open_rec RECORD;
    close_rec RECORD;
    remaining_lots INT;
    temp_lots INT;
    holding_days INT;
    temp_time TIME;
BEGIN
    -- 创建临时表存储结果
    CREATE TEMP TABLE if not exists temp_holding_days (
        open_trade_date DATE,
        close_trade_date DATE,
        open_time TIME,
        close_time TIME,
        commod VARCHAR,
        contract VARCHAR,
        lots INT,
        holding_days INT
    );
	TRUNCATE TABLE temp_holding_days;

    -- 按顺序遍历所有开仓记录
    FOR open_rec IN 
    	SELECT tran_date,commod,instrumentid,volume::int,direction,offsetflag,tradedate,tradetime,tradeid FROM vw_trade
        where account_id=p_acctid and offsetflag='开仓'
        ORDER BY tradedate,tradeid
    LOOP
        remaining_lots := open_rec.volume;
        --raise info '=>%', open_rec;
        -- 查找匹配的平仓记录
        FOR close_rec IN 
            SELECT tran_date,commod,instrumentid,volume::int,direction,offsetflag,tradedate,tradetime,tradeid  
            FROM vw_trade 
            WHERE account_id=p_acctid and instrumentid = open_rec.instrumentid
                AND direction = CASE open_rec.direction WHEN '买' THEN '卖' ELSE '买' END
                AND offsetflag = '平仓'
                AND (tran_date > open_rec.tran_date OR (tran_date = open_rec.tran_date AND tradeid > open_rec.tradeid))
            ORDER BY tradedate, tradeid
        LOOP
            EXIT WHEN remaining_lots <= 0;
            
            -- 计算实际平仓volume
            temp_lots := LEAST(remaining_lots, close_rec.volume);
            
            -- 计算持仓天数
            holding_days := close_rec.tran_date - open_rec.tran_date;
            -- 特殊时间处理逻辑
            IF holding_days = 0 THEN
                temp_time := open_rec.tradetime;
                IF (temp_time BETWEEN '09:00:00' AND '15:15:00') 
                   AND (close_rec.tradetime NOT BETWEEN '09:00:00' AND '15:15:00') THEN
                    holding_days := 1;
                /*ELSE
                    holding_days := '日内';*/
                END IF;
            /*ELSIF holding_days = 1 THEN
                temp_time := open_rec.tradetime;
                IF (temp_time BETWEEN '21:00:00' AND '23:59:59') 
                   AND (close_rec.tradetime < '21:00:00') THEN
                    holding_days := '日内';
                ELSE
                    holding_days := 1;
                END IF;*/
            END IF;

            -- 插入结果
            INSERT INTO temp_holding_days VALUES (
                open_rec.tran_date,
                close_rec.tran_date,
                open_rec.tradetime,
                close_rec.tradetime,
                open_rec.commod,
                open_rec.instrumentid,
                temp_lots,
                holding_days
            );

            -- 更新剩余volume
            remaining_lots := remaining_lots - temp_lots;

        END LOOP;
    END LOOP;

    -- 输出结果
    --RETURN QUERY SELECT * FROM temp_holding_days;
END;
$$;

